const Router = require('koa-router');
const router = new Router();
const tool = require('../../tools/tool')
const {db, QueryTypes} = require("../../models/db");
// 往来欠款表
router.post('/report/qkb', async ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let formData = body.formData
    let tenantId = user.tenantId;
    let buId = formData.buId?formData.buId:0;
    // let categoryName = formData.categoryName?formData.categoryName:'';
    let excel = body.excel
    let sqls = `
        with bills as (
            select tenantId,buId,buName,transTypeName,billDate,
                   case when billType='SALE' then arrearsReal when billType='YS' then totalAmount else 0 end ys,
                   case when billType='RECEIPT' then totalAmount else 0 end sk,
                   case when billType='PUR' then arrearsReal when billType='YF' then totalAmount else 0 end yf,
                   case when billType='PAYMENT' then totalAmount else 0 end fk ,
                   case when billType='YSK' then totalAmount else 0 end yus,
                   case when billType='SALE' then isnull(ysAmount,0) else 0 end yusDikou,
                   case when billType='YFK' then totalAmount else 0 end yuf,
                   case when billType='PUR' then isnull(ysAmount,0) else 0 end yufDikou
            from v_main where tenantId=${tenantId} and billType in('SALE','YS','RECEIPT','PUR','YF','PAYMENT','YSK','YFK') and billState='accounted' and (${buId}=0 or buId=${buId})
            union all
            select tenantId,buId,buName,N'凭证' transTypeName,billDate,
                   case when subjectName in(N'应收账款',N'其他应收款') then price1 else 0 end ys,
                   case when subjectName in(N'应收账款',N'其他应收款') then price2 else 0 end sk,
                   case when subjectName in(N'应付账款',N'其他应付款') then price2 else 0 end yf,
                   case when subjectName in(N'应付账款',N'其他应付款') then price1 else 0 end fk ,
                   case when subjectName in(N'预收款') then price2 else 0 end yus,
                   case when subjectName in(N'预收款') then price1 else 0 end yusDikou,
                   case when subjectName in(N'预付款') then price1 else 0 end yuf,
                   case when subjectName in(N'预付款') then price2 else 0 end yufDikou
            from v_card_info where tenantId=${tenantId} and billState='accounted' and byIid=0
                               and subjectName in(N'应收账款',N'其他应收款',N'应付账款',N'其他应付款',N'预收款',N'预付款') and (${buId}=0 or buId=${buId})
        ) select tenantId, buId, buName, sum(ysYe) ysYe, sum(yfYe) yfYe,sum(yusYe) yusYe,sum(yufYe) yufYe 
          from (
              select tenantId, buId, buName, transTypeName, billDate, ys-sk ysYe, yf-fk yfYe,yus-yusDikou yusYe,yuf-yufDikou yufYe from bills
          ) a group by tenantId, buId, buName order by buName  
    `
    let list = await db.query(sqls, {type: QueryTypes.SELECT})
    if(excel) {
        return tool.doExcelOut(ctx, list)
    }
    else {
        return ctx.suc('', {
            total: list.length,
            list: list
        })
    }
})

module.exports = router;
